CREATE PROCEDURE [dbo].[eod_financial_statements_api]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--****************************************************************************
	--* Income Statement
	--****************************************************************************

	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
    WITH source AS (
		SELECT	  sis_sym_cd
				, sis_exc_cd
				, sis_dt
				, sis_period
				, rn = ROW_NUMBER() OVER (PARTITION BY    sis_sym_cd
														, sis_exc_cd
														, sis_dt
														, sis_period
										  ORDER BY		sis_id DESC)
		FROM	raw.eod_income_statements
	)
	DELETE FROM source WHERE rn > 1;

	-- 2. Step
	-- Merge data Quote into into final table
	MERGE INTO	fin.eod_financial_statements WITH (HOLDLOCK) AS target
	USING		(SELECT   sym_id									AS sis_sym_id
						, cat_id									AS sis_cat_id
						, sis_dt									AS sis_dt
						, sis_tot_revenue							AS sis_tot_rev
						, sis_cogs									AS sis_cogs
						, sis_rnd									AS sis_rnd
						, sis_sga									AS sis_sga
						, sis_non_rec_exp							AS sis_nrec_op_ex
						, sis_other_exp								AS sis_other_op_ex
						, sis_tot_other_income						AS sis_tot_other_inc_ex_net
						, sis_interest_expense						AS sis_int_ex_net
						, sis_income_tax_expense					AS sis_inc_tax_ex
						, sis_minority_interest						AS sis_min_int
						, sis_discnt_ops							AS sis_disc_ops
						, sis_extraordinary_items					AS sis_extra
						, sis_accounting_changes					AS sis_acct_chg
						, sis_other_items							AS sis_other_nrec
						, sis_prfrd_stock_adjust					AS sis_pref_other_adj
						, CASE	
							WHEN abs((sis_tot_revenue - sis_cogs - sis_rnd - sis_sga - sis_non_rec_exp - sis_other_exp 
									+ sis_tot_other_income - sis_interest_expense - sis_income_tax_expense
									+ sis_minority_interest + sis_discnt_ops + sis_extraordinary_items
									+ sis_accounting_changes + sis_other_items) - sis_ni) > 1000000
							THEN 
								1
							ELSE
								0
							END										AS sis_quality_fg
						, (sis_tot_revenue - sis_cogs - sis_rnd - sis_sga - sis_non_rec_exp - sis_other_exp 
							+ sis_tot_other_income - sis_interest_expense - sis_income_tax_expense
							+ sis_minority_interest + sis_discnt_ops + sis_extraordinary_items
							+ sis_accounting_changes + sis_other_items) - sis_ni
																	AS sis_quality_adj
				FROM	raw.eod_income_statements
						INNER JOIN fin.eod_exchanges
							ON (sis_exc_cd = exc_cd)
						INNER JOIN fin.eod_symbols
							ON (	sis_sym_cd	= sym_cd
								AND exc_id		= sym_exc_id)
						INNER JOIN fin.com_categories
							ON (sis_period = cat_desc)) AS source
	ON				target.fis_sym_id = source.sis_sym_id
				AND	target.fis_dt = source.sis_dt
				AND target.fis_cat_id = source.sis_cat_id
	-- 2a: Existing Statement -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.fis_sym_id = source.sis_sym_id
					, target.fis_cat_id = source.sis_cat_id
					, target.fis_dt = source.sis_dt
					, target.fis_is_tot_rev = source.sis_tot_rev
					, target.fis_is_cogs = source.sis_cogs
					, target.fis_is_rnd = source.sis_rnd
					, target.fis_is_sga = source.sis_sga
					, target.fis_is_nrec_op_ex = source.sis_nrec_op_ex
					, target.fis_is_other_op_ex = source.sis_other_op_ex
					, target.fis_is_tot_other_inc_ex_net = source.sis_tot_other_inc_ex_net
					, target.fis_is_int_ex_net = source.sis_int_ex_net
					, target.fis_is_inc_tax_ex = source.sis_inc_tax_ex
					, target.fis_is_min_int = source.sis_min_int
					, target.fis_is_disc_ops = source.sis_disc_ops
					, target.fis_is_extra = source.sis_extra
					, target.fis_is_acct_chg = source.sis_acct_chg
					, target.fis_is_other_nrec = source.sis_other_nrec
					, target.fis_is_pref_other_adj = source.sis_pref_other_adj
					, target.fis_is_quality_fg = source.sis_quality_fg
					, target.fis_is_quality_adj = source.sis_quality_adj
	-- 2b: New Statement -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( fis_sym_id
				, fis_cat_id
				, fis_dt
				, fis_is_tot_rev
				, fis_is_cogs
				, fis_is_rnd
				, fis_is_sga
				, fis_is_nrec_op_ex
				, fis_is_other_op_ex
				, fis_is_tot_other_inc_ex_net
				, fis_is_int_ex_net
				, fis_is_inc_tax_ex
				, fis_is_min_int
				, fis_is_disc_ops
				, fis_is_extra
				, fis_is_acct_chg
				, fis_is_other_nrec
				, fis_is_pref_other_adj
				, fis_is_quality_fg
				, fis_is_quality_adj )
		VALUES	( sis_sym_id
				, sis_cat_id
				, sis_dt
				, sis_tot_rev
				, sis_cogs
				, sis_rnd
				, sis_sga
				, sis_nrec_op_ex
				, sis_other_op_ex
				, sis_tot_other_inc_ex_net
				, sis_int_ex_net
				, sis_inc_tax_ex
				, sis_min_int
				, sis_disc_ops
				, sis_extra
				, sis_acct_chg
				, sis_other_nrec
				, sis_pref_other_adj
				, sis_quality_fg
				, sis_quality_adj );

	--****************************************************************************
	--* Balance Sheet
	--****************************************************************************


	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
    WITH source AS (
		SELECT	  sbs_sym_cd
				, sbs_exc_cd
				, sbs_dt
				, sbs_period
				, rn = ROW_NUMBER() OVER (PARTITION BY    sbs_sym_cd
														, sbs_exc_cd
														, sbs_dt
														, sbs_period
										  ORDER BY		sbs_id DESC)
		FROM	raw.eod_balance_sheets
	)
	DELETE FROM source WHERE rn > 1;


	-- 2. Step
	-- Merge data into into final table
	MERGE INTO	fin.eod_financial_statements WITH (HOLDLOCK) AS target
	USING		(SELECT   sym_id									AS sbs_sym_id
						, cat_id									AS sbs_cat_id
						, sbs_dt									AS sbs_dt
						, sbs_ca_cash_and_eqv						AS sbs_ca_cash_eqiv
						, sbs_ca_st_inv								AS sbs_ca_st_inv
						, sbs_ca_net_recbls							AS sbs_ca_rec_net
						, sbs_ca_invtry								AS sbs_ca_invtry
						, sbs_ca_other_assts						AS sbs_ca_other
						, sbs_nca_lt_inv							AS sbs_nca_lt_inv
						, sbs_nca_ppe								AS sbs_nca_ppe
						, sbs_nca_goodwill							AS sbs_nca_gwill
						, sbs_nca_intgbl_assts						AS sbs_nca_intang_asst
						, sbs_nca_accum_amort						AS sbs_nca_accum_amort
						, sbs_nca_other_assts						AS sbs_nca_other
						, sbs_nca_dfrd_lt_assts_chrg				AS sbs_nca_dfrd
						, sbs_cl_acct_paybl							AS sbs_cl_payb_net
						, sbs_cl_crnt_lt_debt						AS sbs_cl_curr_lt_debt
						, sbs_cl_other								AS sbs_cl_other
						, sbs_ncl_lt_debt							AS sbs_ncl_lt_debt
						, sbs_ncl_min_intr							AS sbs_ncl_min_int
						, sbs_ncl_neg_goodwill						AS sbs_ncl_neg_gwill
						, sbs_ncl_other_liab						AS sbs_ncl_other
						, sbs_ncl_dfrd_lt_liab_chrg					AS sbs_ncl_drfd_lt_liab_chrg
						, sbs_eq_misc_st_opt_wrnt					AS sbs_eq_st_opt_wrnt
						, sbs_eq_redmbl_prfrd_st					AS sbs_eq_redeem_prfrd_st
						, sbs_eq_prfrd_st							AS sbs_eq_prfrd_st
						, sbs_eq_comn_st							AS sbs_eq_comn_st
						, sbs_eq_rtnd_earn							AS sbs_eq_ret_earn
						, sbs_eq_trsry_st							AS sbs_eq_try_st
						, sbs_eq_cap_spls							AS sbs_eq_cap_splus
						, sbs_eq_other_eqty							AS sbs_eq_other
						, sbs_net_tgbl_assts						AS sbs_bs_tng_asst_net
				FROM	raw.eod_balance_sheets
						INNER JOIN fin.eod_exchanges
							ON (sbs_exc_cd = exc_cd)
						INNER JOIN fin.eod_symbols
							ON (	sbs_sym_cd	= sym_cd
								AND exc_id		= sym_exc_id)
						INNER JOIN fin.com_categories
							ON (sbs_period = cat_desc)) AS source
	ON				target.fis_sym_id = source.sbs_sym_id
				AND	target.fis_dt = source.sbs_dt
				AND target.fis_cat_id = source.sbs_cat_id
	-- 2a: Existing Statement -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.fis_sym_id = source.sbs_sym_id
					, target.fis_cat_id = source.sbs_cat_id
					, target.fis_dt = source.sbs_dt
					, target.fis_bs_ca_cash_eqiv = source.sbs_ca_cash_eqiv
					, target.fis_bs_ca_st_inv = source.sbs_ca_st_inv
					, target.fis_bs_ca_rec_net = source.sbs_ca_rec_net
					, target.fis_bs_ca_invtry = source.sbs_ca_invtry
					, target.fis_bs_ca_other = source.sbs_ca_other
					, target.fis_bs_nca_lt_inv = source.sbs_nca_lt_inv
					, target.fis_bs_nca_ppe = source.sbs_nca_ppe
					, target.fis_bs_nca_gwill = source.sbs_nca_gwill
					, target.fis_bs_nca_intang_asst = source.sbs_nca_intang_asst
					, target.fis_bs_nca_accum_amort = source.sbs_nca_accum_amort
					, target.fis_bs_nca_other = source.sbs_nca_other
					, target.fis_bs_nca_dfrd_lt_asst_chrg = source.sbs_nca_dfrd
					, target.fis_bs_cl_payb_net = source.sbs_cl_payb_net
					, target.fis_bs_cl_st_curr_lt_debt = source.sbs_cl_curr_lt_debt
					, target.fis_bs_cl_other = source.sbs_cl_other
					, target.fis_bs_ncl_lt_debt = source.sbs_ncl_lt_debt
					, target.fis_bs_ncl_min_int = source.sbs_ncl_min_int
					, target.fis_bs_ncl_neg_gwill = source.sbs_ncl_neg_gwill
					, target.fis_bs_ncl_other = source.sbs_ncl_other
					, target.fis_bs_ncl_dfrd_lt_liab_chrg = source.sbs_ncl_drfd_lt_liab_chrg
					, target.fis_bs_eq_st_opt_wrnt = source.sbs_eq_st_opt_wrnt
					, target.fis_bs_eq_redeem_prfd_st = source.sbs_eq_redeem_prfrd_st
					, target.fis_bs_eq_prfd_st = source.sbs_eq_prfrd_st
					, target.fis_bs_eq_comn_st = source.sbs_eq_comn_st
					, target.fis_bs_eq_ret_earn = source.sbs_eq_ret_earn
					, target.fis_bs_eq_try_st = source.sbs_eq_try_st
					, target.fis_bs_eq_cap_splus = source.sbs_eq_cap_splus
					, target.fis_bs_eq_other = source.sbs_eq_other
					, target.fis_bs_tng_asst_net = source.sbs_bs_tng_asst_net

	-- 2b: New Statement -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( fis_sym_id
				, fis_cat_id
				, fis_dt
				, fis_bs_ca_cash_eqiv
				, fis_bs_ca_st_inv
				, fis_bs_ca_rec_net
				, fis_bs_ca_invtry
				, fis_bs_ca_other
				, fis_bs_nca_lt_inv
				, fis_bs_nca_ppe
				, fis_bs_nca_gwill
				, fis_bs_nca_intang_asst
				, fis_bs_nca_accum_amort
				, fis_bs_nca_other
				, fis_bs_nca_dfrd_lt_asst_chrg
				, fis_bs_cl_payb_net
				, fis_bs_cl_st_curr_lt_debt
				, fis_bs_cl_other
				, fis_bs_ncl_lt_debt
				, fis_bs_ncl_min_int
				, fis_bs_ncl_neg_gwill
				, fis_bs_ncl_other
				, fis_bs_ncl_dfrd_lt_liab_chrg
				, fis_bs_eq_st_opt_wrnt
				, fis_bs_eq_redeem_prfd_st
				, fis_bs_eq_prfd_st
				, fis_bs_eq_comn_st
				, fis_bs_eq_ret_earn
				, fis_bs_eq_try_st
				, fis_bs_eq_cap_splus
				, fis_bs_eq_other
				, fis_bs_tng_asst_net)
		VALUES	( sbs_sym_id
				, sbs_cat_id
				, sbs_dt
				, sbs_ca_cash_eqiv
				, sbs_ca_st_inv
				, sbs_ca_rec_net
				, sbs_ca_invtry
				, sbs_ca_other
				, sbs_nca_lt_inv
				, sbs_nca_ppe
				, sbs_nca_gwill
				, sbs_nca_intang_asst
				, sbs_nca_accum_amort
				, sbs_nca_other
				, sbs_nca_dfrd
				, sbs_cl_payb_net
				, sbs_cl_curr_lt_debt
				, sbs_cl_other
				, sbs_ncl_lt_debt
				, sbs_ncl_min_int
				, sbs_ncl_neg_gwill
				, sbs_ncl_other
				, sbs_ncl_drfd_lt_liab_chrg
				, sbs_eq_st_opt_wrnt
				, sbs_eq_redeem_prfrd_st
				, sbs_eq_prfrd_st
				, sbs_eq_comn_st
				, sbs_eq_ret_earn
				, sbs_eq_try_st
				, sbs_eq_cap_splus
				, sbs_eq_other
				, sbs_bs_tng_asst_net );
	

	--****************************************************************************
	--* Cash Flow
	--****************************************************************************

	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
	WITH source AS (
		SELECT	  scf_sym_cd
				, scf_exc_cd
				, scf_dt
				, scf_period
				, rn = ROW_NUMBER() OVER (PARTITION BY    scf_sym_cd
														, scf_exc_cd
														, scf_dt
														, scf_period
										  ORDER BY		scf_id DESC)
		FROM	raw.eod_cash_flows
	)
	DELETE FROM source WHERE rn > 1;

	MERGE INTO	fin.eod_financial_statements WITH (HOLDLOCK) AS target
	USING		(SELECT   sym_id									AS scf_sym_id
						, cat_id									AS scf_cat_id
						, scf_dt									AS scf_dt
						, scf_depr									AS scf_op_depr
						, scf_adj_ni								AS scf_op_ni_adj
						, scf_chng_acct_recbls						AS scf_op_rec_chg
						, scf_chng_liab								AS scf_op_liab_chg
						, scf_chng_invtry							AS scf_op_invtry_chg
						, scf_chng_other_ops						AS scf_op_other_chg
						, scf_capex									AS scf_inv_cap_ex
						, scf_inv									AS scf_inv
						, scf_other_inv								AS scf_inv_other
						, scf_div_paid								AS scf_fin_div
						, scf_sal_purch_st							AS scf_fin_sale_purch_st
						, scf_net_borrow							AS scf_fin_borrow_net
						, scf_other_fin								AS scf_fin_other
						, scf_fx_rate_chng							AS scf_fin_fx_chg
						, scf_chng_cash_and_eqv						AS scf_cash_eqiv_chg
				FROM	raw.eod_cash_flows
						INNER JOIN fin.eod_exchanges
							ON (scf_exc_cd = exc_cd)
						INNER JOIN fin.eod_symbols
							ON (	scf_sym_cd	= sym_cd
								AND exc_id		= sym_exc_id)
						INNER JOIN fin.com_categories
							ON (scf_period = cat_desc)) AS source
	ON				target.fis_sym_id = source.scf_sym_id
				AND	target.fis_dt = source.scf_dt
				AND target.fis_cat_id = source.scf_cat_id
	-- 2a: Existing Statement -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.fis_sym_id = source.scf_sym_id
					, target.fis_cat_id = source.scf_cat_id
					, target.fis_dt = source.scf_dt
					, target.fis_cf_op_depr = source.scf_op_depr
					, target.fis_cf_op_ni_adj = source.scf_op_ni_adj
					, target.fis_cf_op_rec_chg = source.scf_op_rec_chg
					, target.fis_cf_op_liab_chg = source.scf_op_liab_chg
					, target.fis_cf_op_invtry_chg = source.scf_op_invtry_chg
					, target.fis_cf_op_other_chg = source.scf_op_other_chg
					, target.fis_cf_inv_cap_ex = source.scf_inv_cap_ex
					, target.fis_cf_inv = source.scf_inv
					, target.fis_cf_inv_other = source.scf_inv_other
					, target.fis_cf_fin_div = source.scf_fin_div
					, target.fis_cf_fin_sale_purch_st = source.scf_fin_sale_purch_st
					, target.fis_cf_fin_borrow_net = source.scf_fin_borrow_net
					, target.fis_cf_fin_other = source.scf_fin_other
					, target.fis_cf_fx_chg = source.scf_fin_fx_chg
					, target.fis_cf_cash_eqiv_chg = source.scf_cash_eqiv_chg
	-- 2b: New Statement -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( fis_sym_id
				, fis_cat_id
				, fis_dt
				, fis_cf_op_depr
				, fis_cf_op_ni_adj
				, fis_cf_op_rec_chg
				, fis_cf_op_liab_chg
				, fis_cf_op_invtry_chg
				, fis_cf_op_other_chg
				, fis_cf_inv_cap_ex
				, fis_cf_inv
				, fis_cf_inv_other
				, fis_cf_fin_div
				, fis_cf_fin_sale_purch_st
				, fis_cf_fin_borrow_net
				, fis_cf_fin_other
				, fis_cf_fx_chg
				, fis_cf_cash_eqiv_chg)
		VALUES	( scf_sym_id
				, scf_cat_id
				, scf_dt
				, scf_op_depr
				, scf_op_ni_adj
				, scf_op_rec_chg
				, scf_op_liab_chg
				, scf_op_invtry_chg
				, scf_op_other_chg
				, scf_inv_cap_ex
				, scf_inv
				, scf_inv_other
				, scf_fin_div
				, scf_fin_sale_purch_st
				, scf_fin_borrow_net
				, scf_fin_other
				, scf_fin_fx_chg
				, scf_cash_eqiv_chg );

END
